package com.ash.other;

import com.ash.domain.Student;
import com.ash.util.ConnectionUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.Objects;

/**
 * @encoding : UTF-8
 * @Time : 2020/12/6 17:14
 * @Author : Chen Chao
 * @File : connection.com.ash.other.Test02.java
 * @Software : IntelliJ IDEA
 * @since : 1.8
 * 存储过程
 */
public class Test02 {
    public static void main(String[] args) {
        test01();
        int id=72;
        test02(id);
    }

    /**
     * 无参存储过程调用
     */
    private static void test01() {
        /*建立连接*/
        ArrayList<Student> students;
        Connection connection = null;
        CallableStatement call = null;
        ResultSet resultSet = null;
        try {
            connection = ConnectionUtil.getConnection();
            /*新建对象，注意格式*/
            call = Objects.requireNonNull(connection).prepareCall("{ call getStudent()}");
            resultSet = call.executeQuery();
            students = new ArrayList<>();

            while (resultSet.next()) {
                Student student = new Student();
                /*取出数据*/
                student.setId(resultSet.getInt("id"));
                student.setName(resultSet.getString("name"));
                student.setAge(resultSet.getInt("age"));
                students.add(student);
            }
            students.forEach(System.out::println);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionUtil.close(connection, call, resultSet);
        }

    }

    /**
     * 有参存储过程
     * @param id 学生id
     */
    private static void test02(int id) {
        Connection connection = null;
        CallableStatement call = null;
        String sql = "{call getNameById(?,?)}";
        try {
            connection = ConnectionUtil.getConnection();
            call = Objects.requireNonNull(connection).prepareCall(sql);
            call.setInt(1, id);
            /*相当于注册一个箱子*/
            call.registerOutParameter(2, Types.VARCHAR);
            call.execute();
            /*执行后取出箱子里的东西*/
            String string = call.getString(2);
            System.out.println("id为" + id + "的学生姓名为:" + string);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionUtil.close(connection,call,null);

        }
    }

}
